﻿#encoding: utf-8
=begin

Usage:      将旧tutem系统的用户关系迁移到新系统去
arguments:  ruby update-tags.rb

=end

load 'rds_api.rb'
load 'tutem_utils.rb'

tags = ['all', 'matched', 'with_unionid', 'with_openid', 'ordered', 'paid', 'binded', 'unmatched']
def get_users_for prefix, tag, fields

  @rds = @rds_fc
  @rds = @rds_tt if prefix == 'tutem_mall.eb'

  users = []

  case tag
    when 'all'
      sql = "select #{fields} from #{prefix}_user_extended order by uid asc;"
    when 'with_unionid'	#带有union_id的eb_user
      sql = "select #{fields} from #{prefix}_user_extended where uid in ( select uid from #{prefix}_wechat_user where unionid is not null) order by uid asc;"
    when 'with_openid'	#带有open_id的eb_user
      sql = "select #{fields} from #{prefix}_user_extended where uid in ( select uid from #{prefix}_wechat_user where openid is not null) order by uid asc;"
    when 'ordered'	#看看下过订单的uid都有谁
      sql = "select #{fields} from #{prefix}_user_extended where uid in ( select uid from #{prefix}_store_order group by uid) order by uid asc;"
    when 'paid'	#下过订单并且支付成功的用户; 实际上等同于 _user 中 where pay_coun > 0
      sql = "select #{fields} from #{prefix}_user_extended where uid in ( select uid from #{prefix}_store_order where paid = 1 group by uid) order by uid asc;"
    when 'binded'	#绑定手机号的用户
      sql = "select #{fields} from #{prefix}_user_extended where phone is not null and phone != '' order by uid asc;"
    when 'is_promoter'  #开通了推广账号的用户
      sql = "select #{fields} from #{prefix}_user_extended where is_promoter = 1 order by uid asc;"
    when 'matched'  #新老系统已经配对的用户
      sql = "select #{fields} from #{prefix}_user_extended where #{ prefix == 'tutem_mall.eb' ? 'old_uid' : 'tutem_uid' } > 0 order by uid asc;"
    when 'unmatched'#新老系统尚未配对的用户
      sql = "select #{fields} from #{prefix}_user_extended where #{ prefix == 'tutem_mall.eb' ? 'old_uid' : 'tutem_uid' } = 0 order by uid asc;"
    when 'no_promoter'  #没有推荐人
      sql = "select #{fields} from #{prefix}_user_extended where spread_uid = 0 order by uid asc;"
    when 'has_promoter'  #有推荐人
      sql = "select #{fields} from #{prefix}_user_extended where spread_uid > 0 order by uid asc;"
    when 'has_promoter_and_not_promoter'
      sql = "select #{fields} from #{prefix}_user_extended where spread_uid > 0 and is_promoter = 0 order by uid asc;"
    else
      return []
  end

  res = queryRds sql
  res.each { |user| users += [user] }

  return users

end

def get_uids_for prefix, tag
  uids = []
  users = get_users_for prefix, tag, 'uid'
  users.each { |user| uids += [user['uid']] }
  return uids
end

def get_tutem_uids_for tag
  uids = []
  users = get_users_for 'estbio_crmeb.dyh', tag, 'tutem_uid'
  users.each { |user| uids += [user['tutem_uid']] }
  return uids
end

=begin 

#测试基础函数，获取用户数据集
tags.each { |tag| get_users_for 'estbio_crmeb.dyh', tag, 'uid, phone, unionid, openid, routine_openid' }
tags.each { |tag| get_users_for 'tutem_mall.eb', tag, 'uid, phone, unionid, openid, routine_openid' }

#测试基础函数，获取用户ID表
tags.each { |tag| uids = get_uids_for 'estbio_crmeb.dyh', tag; puts uids.to_s }
tags.each { |tag| uids = get_uids_for 'tutem_mall.eb', tag; puts uids.to_s }

=end

#用于更新映射关系 maps = [{'old_uid': 1, 'tutem_uid': 2, 'something': ...}...]
#eg: update_user_maps_for [{'old_uid' => 181, 'tutem_uid' => 26}]
#mode: 'o' old; 't' tutem; 'f' force
def update_user_maps_for maps, mode 

  maps.each do |map|

    old_uid = map['old_uid']
    tutem_uid = map['tutem_uid']

    force = (mode.include? 'f') ? true : false

    if mode.include? 'o'
      @rds = @rds_fc
      sqls = []
      sqls += [ "update estbio_crmeb.dyh_user set tutem_uid = #{tutem_uid} where uid = #{old_uid} #{ force ? '': 'and tutem_uid = 0' } ;" ]
      sqls += [ "update estbio_crmeb.dyh_wechat_user set tutem_uid = #{tutem_uid} where uid = #{old_uid} #{ force ? '' : 'and tutem_uid = 0' } ;" ]

      commitTrans sqls
    end

    @rds = @rds_tt
    sqls = []

    if mode.include? 'o'
      sqls += [ "update tutem_mall.dyh_user set tutem_uid = #{tutem_uid} where uid = #{old_uid} #{ force ? '' : 'and tutem_uid = 0' } ;" ]
      sqls += [ "update tutem_mall.dyh_wechat_user set tutem_uid = #{tutem_uid} where uid = #{old_uid} #{ force ? '' : 'and tutem_uid = 0' } ;" ]
    end
    if mode.include? 't'
      sqls += [ "update tutem_mall.eb_user set old_uid = #{old_uid} where uid = #{tutem_uid} #{ force ? '' : 'and old_uid = 0' } ;" ]
      sqls += [ "update tutem_mall.eb_wechat_user set old_uid = #{old_uid} where uid = #{tutem_uid} #{ force ? '' : 'and old_uid = 0' } ;" ]
    end

    commitTrans sqls

  end 

end

#   根据指定栏在新版用户扩展表中寻找旧版用户扩展表并建立匹配关系
#   eg:    match_users_by 'headimgurl' 
def match_users_by field_name

  #其实这些额外信息都是为了验证而用的，嗨！
  extra_fields = ", nickname as s_nickname, real_name as s_realname, phone as s_phone, avatar as s_avatar, headimgurl as s_headimgurl, country as s_country, province as s_province, city as s_city, sex as s_sex, pay_count as s_pcount, spread_nickname as s_spreader"

  #建立旧系统的nickname到uid, #{field_name}到uid的映射
  qualified = []
  unmatched = get_users_for 'estbio_crmeb.dyh', 'unmatched', "uid, #{field_name} #{extra_fields}"
  unmatched.each do |u|
    if u[field_name].to_s != ''
      h = {}
      h.store 'old_uid', u['uid']
      h.store field_name, u[field_name]
      h.store 's_nickname', u['s_nickname']
      h.store 's_realname', u['s_realname']
      h.store 's_phone', u['s_phone']
      h.store 's_avatar', u['s_avatar']
      h.store 's_headimgurl', u['s_headimgurl']
      h.store 's_country', u['s_country']
      h.store 's_province', u['s_province']
      h.store 's_city', u['s_city']
      h.store 's_sex', u['s_sex']
      h.store 's_pcount', u['s_pcount']
      h.store 's_spreader', u['s_spreader']
      qualified += [h]
    end
  end

  @rds = @rds_tt

  extra_fields = ", nickname as t_nickname, real_name as t_realname, phone as t_phone, avatar as t_avatar, headimgurl as t_headimgurl, country as t_country, province as t_province, city as t_city, sex as t_sex, pay_count as t_pcount, spread_nickname as t_spreader"
  tobe_matched = []
  qualified.each do |user|
    sql = "select uid #{extra_fields} from tutem_mall.eb_user_extended where #{field_name} = '#{@rds.escape user[field_name]}' and old_uid = 0 and user_type != 'h5' limit 1;"
    res = queryRds sql
    res.each do |r|

      tutem_user = {}

      tutem_user.store('old_uid',user['old_uid'])
      tutem_user.store 's_nickname', user['s_nickname']
      tutem_user.store 's_realname', user['s_realname']
      tutem_user.store 's_phone', user['s_phone']
      tutem_user.store 's_avatar', user['s_avatar']
      tutem_user.store 's_headimgurl', user['s_headimgurl']
      tutem_user.store 's_country', user['s_country']
      tutem_user.store 's_province', user['s_province']
      tutem_user.store 's_city', user['s_city']
      tutem_user.store 's_sex', user['s_sex']
      tutem_user.store 's_pcount', user['s_pcount']
      tutem_user.store 's_spreader', user['s_spreader']

      tutem_user.store('tutem_uid',r['uid'])
      tutem_user.store 't_nickname', r['t_nickname']
      tutem_user.store 't_realname', r['t_realname']
      tutem_user.store 't_phone', r['t_phone']
      tutem_user.store 't_avatar', r['t_avatar']
      tutem_user.store 't_headimgurl', r['t_headimgurl']
      tutem_user.store 't_country', r['t_country']
      tutem_user.store 't_province', r['t_province']
      tutem_user.store 't_city', r['t_city']
      tutem_user.store 't_sex', r['t_sex']
      tutem_user.store 't_pcount', r['t_pcount']
      tutem_user.store 't_spreader', r['t_spreader']

      tobe_matched += [tutem_user]

      break
    end
  end

  puts "match users by #{field_name}: #{tobe_matched.size}/#{qualified.size}"

  return tobe_matched

end

#用来查看并做人工校验
def save_matched_as_html recs

  html = ""
  html += "<html><head>tobe-matched</head><body>"

  html += "<table>"
  html += "<tr>
      <td>idx</td>
      <td>mc</td>
      <td>old_uid</td>
      <td>nickname(realname)</td>
      <td>img</td>
      <td>paid</td>
      <td>spreader</td>
      <td>tutem_uid</td>
      <td>nickname(realname)</td>
      <td>img</td>
      <td>paid</td>
      <td>spreader</td>
    </tr>"

  recs.size.times do |idx|

    rec = recs[idx]

    match_count = 0
    match_count -= 1 if rec['s_nickname'] != rec['t_nickname']
    match_count -= 1 if rec['s_realname'] != rec['t_realname']
    match_count -= 1 if rec['s_phone'] != rec['t_phone']
    match_count -= 1 if rec['s_country'] != rec['t_country']
    match_count -= 1 if rec['s_province'] != rec['t_province']
    match_count -= 1 if rec['s_city'] != rec['t_city']
    match_count -= 1 if rec['s_sex'] != rec['t_sex']

    t_img = "<img src='" + rec['t_headimgurl'] + "'></img>"
    s_img = "<img src='" + rec['s_headimgurl'] + "'></img>"

    html += "<tr>
        <td>#{idx}</td>
        <td>#{'<font color=red>' if match_count != 0}#{match_count}#{'</font>' if match_count != 0}</td>
        <td>#{rec['old_uid']}</td>
        <td>#{rec['s_nickname']}(#{rec['s_realname']})</td>
        <td>#{s_img}</td>
        <td>#{rec['s_pcount']}</td>
        <td>#{rec['s_spreader']}</td>
        <td>#{rec['tutem_uid']}</td>
        <td>#{rec['t_nickname']}(#{rec['t_realname']})</td>
        <td>#{t_img}</td>
        <td>#{rec['t_pcount']}</td>
        <td>#{rec['t_spreader']}</td>
    </tr>"

  end
  html += "</table>"

  html += "</body></html>"

  File.open("tobe-matched.html","w:UTF-8") { |f| f.write html}

end

def match_by_order_phone

  buyer_uids = get_uids_for 'estbio_crmeb.dyh', 'ordered'
  matched_uids = get_uids_for 'estbio_crmeb.dyh', 'matched'
  to_match_uids = buyer_uids - matched_uids

  #建立旧系统的user_phone到uid的映射
  uids_string = to_match_uids.join(',')
  sql = "select uid, user_phone from estbio_crmeb.dyh_store_order where uid in (#{uids_string}) group by user_phone;"
  phone_map = {}
  res = queryRds sql
  res.each do |r|
    phone_map.store(r['user_phone'],r['uid'])
  end

  #遍历新系统订单的手机号，建立新旧系统映射
  @rds = @rds_tt
  sql = "select uid, user_phone from tutem_mall.eb_store_order group by user_phone;"
  uid_map = {}
  target_users = []
  res = queryRds sql
  res.each do |r|
    uid_map.store(r['uid'], phone_map[r['user_phone']]) if !phone_map[r['user_phone']].nil?
    user_phone = r['user_phone']
    if !phone_map[user_phone].nil?
      user = {}
      user['tutem_uid'] = r['uid']
      user['old_uid'] = phone_map[user_phone]
      target_users += [user]
    end
  end

  update_user_maps_for target_users, 'ot'

end

def extend_wechat_user

  matched_users = get_users_for 'tutem_mall.eb', 'matched', 'uid, old_uid'

  sqls = []
  matched_users.each do |u|
    sql = "update `tutem_mall`.`eb_wechat_user` set `old_uid` = #{u['old_uid']} where `uid` = #{u['uid']};"
    #sql = "update `tutem_mall`.`dyh_wechat_user` set `tutem_uid` = #{u['uid']} where `uid` = #{u['old_uid']};" 其实是复制过去的
    sqls += [sql]
  end
  @rds = @rds_tt
  commitTrans sqls

  sqls = []
  matched_users.each do |u|
    sql = "update `estbio_crmeb`.`dyh_wechat_user` set `tutem_uid` = #{u['uid']} where `uid` = #{u['old_uid']};"
    sqls += [sql]
  end
  @rds = @rds_fc
  commitTrans sqls

end

#可以根据old_uid获得tutem_uid
def o2t_map
  map = {}
  map.store 0,0
  @rds = @rds_tt
  queryRds('select uid, old_uid from tutem_mall.eb_user where old_uid > 0 order by old_uid asc').each do |q| 
    map.store(q['old_uid'],q['uid'])
  end
  return map
end

#根据uid获得spread_uid
def u2s_map
  map = {}
  map.store 0,0
  @rds = @rds_tt
  queryRds('select uid, spread_uid from tutem_mall.eb_user order by spread_uid').each do |q| 
    map.store(q['uid'],q['spread_uid'])
  end
  return map
end

#打印user信息
def get_user_info prefix, uid, fields
  @rds = @rds_fc
  @rds = @rds_tt if prefix == 'tutem_mall.eb'
  sql = "select #{fields} from #{prefix}_user_extended where uid = #{uid} limit 1;"
  res = queryRds sql
  user = {}
  res.each { |r| user = r; break; }
  return user
end

#可以根据tutem_uid获得old_uid
def t2o_map
  map = {}
  @rds = @rds_tt
  queryRds('select uid, old_uid from tutem_mall.eb_user where old_uid > 0 order by uid asc').each do |q| 
    map.store(q['uid'],q['old_uid'])
  end
  return map
end

def create_unmatched_users

  @rds = @rds_tt
  sql = "INSERT INTO `tutem_mall`.`eb_user` (
      `account`, `pwd`, `real_name`, `birthday`, `card_id`, `mark`, `partner_id`, `group_id`, 
      `nickname`, `avatar`, 
      `phone`, `add_time`, `add_ip`, `last_time`, `last_ip`, `now_money`, `brokerage_price`, `integral`, 
      `sign_num`, `status`, `level`, `spread_uid`, `spread_time`, `user_type`, `is_promoter`, `pay_count`, `spread_count`, `clean_time`, 
      `addres`, `adminid`, `login_type`, `register_code`, `agency`, `referral_code`, `old_uid`
    ) SELECT 
        `account`, `pwd`, `real_name`, `birthday`, `card_id`, `mark`, `partner_id`, 999991 as `group_id`, 
        `nickname`, `avatar`, 
        `phone`, `add_time`, `add_ip`, `last_time`, `last_ip`, `now_money`, `brokerage_price`, `integral`, 
        `sign_num`, `status`, `level`, `spread_uid`, `spread_time`, `user_type`, `is_promoter`, `pay_count`, `spread_count`, `clean_time`, 
        `addres`, `adminid`, `login_type`, '' as `register_code`, `company` as `agency`, '' as `referral_code`, `uid` as `old_uid`
      FROM `tutem_mall`.`dyh_user` where tutem_uid = 0 order by uid asc;"
  queryRds sql

  sql = "INSERT INTO `tutem_mall`.`eb_wechat_user` (
      `unionid`, `openid`, `routine_openid`, 
      `nickname`, `headimgurl`, 
      `sex`, `city`, `language`, `province`, `country`, `remark`, `groupid`, `tagid_list`, `subscribe`, `subscribe_time`, `add_time`, 
      `stair`, `second`, `order_stair`, `order_second`, `now_money`, `session_key`, `user_type`, `old_uid`
    ) SELECT
        `unionid`, `openid`, `routine_openid`, 
        `nickname`, `headimgurl`, 
        `sex`, `city`, `language`, `province`, `country`, `remark`, `groupid`, `tagid_list`, `subscribe`, `subscribe_time`, `add_time`, 
        `stair`, `second`, `order_stair`, `order_second`, `now_money`, `session_key`, `user_type`, `uid` as `old_uid`
      FROM `tutem_mall`.`dyh_wechat_user` where tutem_uid = 0 order by uid asc;"

  queryRds sql

  users = []
  sql = "select * from tutem_mall.eb_user where group_id = 999991;"
  res = queryRds sql
  res.each { |user| users += [user] }

  return users

end

def migrate_unmatched

  #建立数据，这时候记录中的spread_uid仍然是旧的
  created = create_unmatched_users   

  #更新spread_uid,修正临时标志
  o2t = o2t_map
  @rds = @rds_tt
  user_maps = []
  created.each do |user|
    tutem_uid = user['uid']
    old_suid = user['spread_uid']
    new_suid = o2t[old_suid]
    sql ="update tutem_mall.eb_user set group_id = 0 , spread_uid = #{new_suid} where uid = #{tutem_uid};" #同时修正了999991
    queryRds sql
    map = {}
    map.store "tutem_uid", tutem_uid
    map.store "old_uid", user['old_uid']
    user_maps += [map]
  end

  #更新源数据中的user_map
  update_user_maps_for user_maps, 'o'

end


# 检查是否有在老系统中推荐关系，但在新系统中没有登记的，这种情况应该只在前７９３条记录中有
def recover_spread_uid

  o2t = o2t_map
  uids = get_uids_for 'estbio_crmeb.dyh', 'has_promoter'
  uids_str = uids.join(',')

  @rds = @rds_tt
  sql = "select * from tutem_mall.eb_user where old_uid in (#{uids_str} ) and spread_uid = 0;"
  res = queryRds sql
  idx = 0
  sqls = []
  res.each do |r|
    print '.'
    tutem_uid = r['uid']
    old_uid = r['old_uid']
    spread_uid = 0  #这里必然为零
    old_suid = 0    #为了能跳出循环使用
    queryRds("select spread_uid from tutem_mall.dyh_user where uid = #{old_uid}").each do |q|
      old_suid = q['spread_uid']
      spread_uid = o2t[old_suid]
    end
    #puts "ERROR: #{tutem_uid} vs #{old_uid}" if tutem_uid != o2t[old_uid]
    sqls += ["update tutem_mall.eb_user set spread_uid = #{spread_uid} where uid = #{tutem_uid}; #old_uid=#{old_uid} old_suid=#{old_suid}"]
    idx += 1
  end

  #sqls.each { |sql| puts sql }
  commitTrans sqls

  puts "\ndone. #{idx}/#{uids.size}"

end

def recover_promoter

  uids1 = get_tutem_uids_for 'is_promoter' #这里取老版数据，返回是新数据的uid格式
  uids2 = get_uids_for 'tutem_mall.eb', 'is_promoter'

  uids = uids1 - uids2

  sqls = []
  idx = 0
  uids.each do |uid|
    print '.'
    #user = get_user_info 'tutem_mall.eb', uid, 'uid, nickname, phone, is_promoter'
    #ap user
    sqls += ["update tutem_mall.eb_user set is_promoter = 1 where uid = #{uid};"]
    idx += 1
  end

  @rds = @rds_tt
  #sqls.each { |sql| puts sql }
  commitTrans sqls

  puts "\ndone. #{idx}/#{uids.size}"

end

def recover_openid

  #查找具有旧系统数据但openid为空的用户
  @rds = @rds_tt
  recs = []
  sql = "select uid, old_uid from tutem_mall.eb_wechat_user where old_uid > 0 and openid is null order by uid asc"
  old_uids = []
  queryRds(sql).each do |r|
    rec = {}
    rec.store 'tutem_uid', r['uid']
    rec.store 'old_uid', r['old_uid']
    recs += [rec]
    old_uids += [r['old_uid']]
  end

  @rds = @rds_fc
  old_uids_str = old_uids.join(',')
  sql = "select uid, tutem_uid, openid from estbio_crmeb.dyh_user_extended where uid in (#{old_uids_str}) order by tutem_uid asc"
  idx = 0
  to_updated = []
  someones = []
  queryRds(sql).each do |r|
    rec = recs[idx]
    rec.store 'tutem_uid2',r['tutem_uid']
    rec.store 'old_uid2',r['uid']
    rec.store 'openid',r['openid']
    recs[idx] = [rec]
    to_updated += [rec] if !r['openid'].nil?
    someones += [rec] if r['openid'].nil?
    idx += 1
  end

  sqls = []
  idx = 0
  to_updated.each do |t|
    print '.'
    sqls += ["update tutem_mall.eb_wechat_user set openid ='#{@rds.escape t['openid']}' where uid = #{t['tutem_uid']}; #old_uid: #{t['old_uid']}"]
    idx += 1
  end

  @rds = @rds_tt
  #sqls.each { |sql| puts sql }
  commitTrans sqls

  puts "\ndone. #{idx}/#{to_updated.size}"

end

#清除所有的用户金蛋奖励记录
#再根据已收货的订单重新给is_promoter的用户发放金蛋奖励记录
def reset_user_bills

  @rds = @rds_tt

  #删除原先的所有推广佣金发放记录
  sql = "delete from tutem_mall.eb_user_bill where pm = 1 and category = 'now_money' and type = 'brokerage';"
  queryRds sql

  #寻找所有有推广者标识的用户号
  uids = get_uids_for 'tutem_mall.eb', 'has_promoter_and_not_promoter'
  uids_str = uids.join(',')
 
  #给所有
  sql = "select id, uid, real_name as user_name, total_price, pay_price, total_postage, pay_time from tutem_mall.eb_store_order where uid in (#{uids_str}) and paid = 1 and status >=2 and refund_status = 0"

  u2s = u2s_map
  sqls = []

  idx = 0
  spread_balance = {}
  queryRds(sql).each do |order|

    uid = order['uid']
    spread_uid = u2s[uid]

    pay_price = order['pay_price'] #这是实际支付的金额（扣除了积分，算完折扣，包含运费）
    postage = order['total_postage']
    actual_paid = pay_price - postage

    pay_time = order['pay_time']
    user_name = order['user_name']
    order_id = order['id']

    if spread_uid > 0

      print ".#{spread_uid}."

      incentive = actual_paid * 0.2

      if spread_balance[spread_uid].nil?
        spread_balance.store spread_uid, incentive
      else
        balance = spread_balance[spread_uid] + incentive
        spread_balance.store spread_uid, balance
      end

      balance = spread_balance[spread_uid]
      actual_paid = actual_paid.round(2).to_f
      balance = balance.round(2).to_f
      incentive = incentive.round(2).to_f

      msg = "#{user_name}确认收到订单#{order_id}总额#{actual_paid}元，获得图贝#{incentive}枚"
      
      sqlu = "INSERT INTO tutem_mall.eb_user_bill ( uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take) VALUES ( #{spread_uid}, 'order_id##{order_id}', 1, '一级推荐奖励', 'now_money', 'brokerage', #{incentive}, #{balance}, '#{msg}', #{pay_time}, 1, 0);"
      sqls += [sqlu]

    else
      print "x"
    end
    idx += 1

  end

  #sqls.each { |sql| puts sql }
  commitTrans sqls

  @rds = $rds_fc
  puts "\ndone. #{sqls.size}/#{idx}"

end

def reset_now_money_bills

    puts 'reset now_money bills...'

    $rds = @rds_tt
    sql = "update tutem_mall.eb_user set now_money = 0, brokerage_price = 0;"
    queryRds sql

    #get all user should reset no_money bills
    sql = "select uid, count(*) as recs from tutem_mall.eb_user_bill where category = 'now_money' and type in ('recharge', 'brokerage', 'system_add', 'pay_product_refund' 'pay_product', 'system_sub', 'extract') group by uid"
    uids = []
    res = queryRds sql
    res.each do |r|
        uids += [r['uid']]
    end

    #所有的交易都会记录pay_money，但有图贝账户的会显示图贝余额，这里先不处理，到时候看

    balance_hash = {}
    uids.each do |uid|

        sql = "select id, number, type, from_unixtime(add_time) as created_at from tutem_mall.eb_user_bill  where category = 'now_money' and type in ('recharge', 'brokerage', 'system_add', 'pay_product_refund', 'pay_product', 'system_sub', 'extract') and uid = #{uid} order by add_time asc"
        res = queryRds sql

        res.each do |r|

            bill_id = r['id']
            type = r['type']
            created_at = r['created_at']
            change = 1 #
            change = -1 if type == 'pay_product' || type == 'system_sub' || type == 'extract' #其实用pm也可以
            amount = r['number'] * change
            if balance_hash[uid].nil?
                balance_hash.store uid, amount 
            else
                balance_hash.store uid, balance_hash[uid] + amount
            end

            balance = balance_hash[uid]
            log = "#{created_at} #{bill_id} #{type} #{amount.round(2).to_f} #{balance.round(2).to_f}" #puts log if uid == 1
            usql = "update tutem_mall.eb_user_bill set balance = #{balance.round(2).to_f} where id = #{bill_id}"
            queryRds usql

        end

    end

    balance_hash.each do |uid, balance|
        sql = "update tutem_mall.eb_user set now_money = #{balance.round(2).to_f} where uid = #{uid}"
        queryRds sql
    end

    puts "\ndone"
end

#n 推广者level3的处理以及推荐代码的处理
#n 后期订单对推广者的20%赠送处理
#n 对推广者关联销售的标记和处理
#n 改造海报代码生成方法，让后台可以生成
#n 普通用户历史订单及pay_count的处理

=begin

#1  将所有带有unionid的用户，标记出新系统的uid, 
#   82条记录自动迁移
match_users_by 'unionid'

#2  匹配订单手机号
#   两条记录自动迁移
match_by_order_phone

#3  ，为wechat_user也建立tutem_uid或old_uid
#   这是个临时补丁，为此改写了一个保存函数
extend_wechat_user

#4  匹配real_name
#   无记录被匹配
#   手工迁移1条记录
match_users_by 'real_name'

#5  匹配headimgurl
#   无记录被匹配
match_users_by 'headimgurl' #可惜没有比较图像相似度的API,不过就算有，也是很慢的500*500次比较

#6 找nickname相似的记录
matched = match_users_by 'nickname'
#save_matched_as_html matched #对于这种非唯一性的比较必须慎重, 经过肉眼排查后可人工干预旧系统数据令其排除结果之外
#共14条记录，其中三条图像不一致的，在旧系统中将nickname加上-a
#手工迁移11条记录
update_user_maps_for matched, 'ot'

#7 先验证一个事情，系统是靠什么标识给推广者发奖励的
#推广者如果没有is_promoter标识，那么是不发钱的
#如果有了这个标识，当用户确认收货时，金额发放到brokerage_price之中

#8  通过导入导出将fc中的dyh数据表复制到tt中,否则多条插入语句无法工作
#   但是这个数据只能用于首次复制，不应该被用于其它地方


#9  准备将未匹配的记录复制到数据中,这里最有意思的地方在于spread_uid尚不知道
#   插入前groupid设为999991，所有groupid为999991的记录，spread_uid都是需要重新更正的 
#   为了将来能顺利更换为正确的新的spread_uid, spread_uid可填写168168
migrate_unmatched

#10 在新系统中恢复推荐关系
#   共完成46条记录
recover_spread_uid

#11 在新系统中恢复推广人身份
#   共完成五条记录，这里面的主要问题在旧系统的一些测试用户
recover_promoter

#12 将openid复制到现有用户，因为有些早期匹配用户未复制openid
#   共95个用户，另外3个早期用户是h5用户没有openid 1082,1092,1152
recover_openid

#13 普通用户spread_count，pay_count的更新
update_spread_count

#14 删除所有bill记录后重建佣金获得数据
reset_user_bills

#15 测试付款五天后对已发货订单自动收货
confirm_arrival_after 5
=end

update_spread_count
reset_user_bills
reset_now_money_bills
confirm_arrival_after 5
